package cn.telling.telMarketCentre.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import cn.telling.common.groupBuy.GroupBuyStatus;
import cn.telling.common.util.TellingConstance;
import cn.telling.sellerCenter.vo.ShopProductAreaVo;
import cn.telling.telMarketCentre.dao.ITelMarketCentreDao;
import cn.telling.telMarketCentre.vo.ShopProductStockNumControlVo;
import cn.telling.utils.BaseUtil;
import cn.telling.utils.StringHelperTools;
/**
 * @Description:通讯市场产品中心Dao处理
 * @author liugeng
 *
 */
@Repository("telMarketCentreDao")
public class TelMarketCentreDaoImpl implements ITelMarketCentreDao {
	private static Logger logger = Logger.getLogger(TelMarketCentreDaoImpl.class);
	@Autowired
	@Qualifier("film-template")
	private JdbcTemplate jdbcTemplate;
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	/**通讯市场产品已上架、已下架产品  通讯市场卖家*/
	@Override
	public List<ShopProductAreaVo> getTelMarketSaleListByStatus(String userId,String status, String conditions) {
		String sql = "with productData as( " +
				 " select sp.id as shopProductId, sp.productId, pm.productName, sp.supplyId, sa.id as supplyAreaId," +
				 " sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts," +
				 " sa.onShelfTime, sa.offShelfTime" +
				 " from shop_product sp " +
				 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " inner join supply s on sp.supplyId = s.id and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
				 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " where s.user_id = ? and sa.status = ? and sa.saleStatus <> '1' " +
				 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " and pm.saleType = '" + TellingConstance.SALETYPE_2 + "' and lower(replace(pm.productName, ' ', '')) like ? " +
				 " group by sp.id, sp.productId, pm.productName, sp.supplyId, sa.id, sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime " +
				 "), " +
				 "promotiondData as (" +
				 " select pdh.productId shopProductId, pdh.status, nvl(pdh.partiQuantity,0) - sum(nvl(od.product_num, 0)) promotiondNum " +
				 " from promotionm_hjh pmh " +
				 " inner join promotiond_hjh pdh on pmh.id = pdh.pId " +
				 " left join order_detail0 od  on od.active_id = pdh.id "
				 //+"and (od.order_type = '" + OrderConstantValues.ORDER_TYPE_13 +"' or od.order_type = '" + OrderConstantValues.ORDER_TYPE_19 + "')"
				 +" and  od.order_status <> '05' and od.order_status <> '06' and od.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " where pmh.isChecked = '2' and (pmh.protype = 2 or pmh.supplyid is not null) and (pdh.status = " + GroupBuyStatus.waitPulish.index() + " or pdh.status = " + GroupBuyStatus.inProcess.index() + " or pdh.status = " + GroupBuyStatus.intime.index() + ") " +
				 " group by pdh.productId, pdh.status, pdh.partiQuantity " +
				 "), " +
				 "seckillData as (" +
				 " select sk.productId shopProductId, sk.statu, sum(nvl(sk.order_num,0)) seckillNum " + 
				 " from seckill_hjh sk " +
				 " where sk.statu = 1 or sk.statu = 3 " +
				 " group by sk.productId, sk.statu " +
				 ") " +
				 "select pd.shopProductId, pd.productId, pd.productName, pd.supplyId, pd.supplyAreaId," +
				 " pd.priceRetailOnline,(nvl(pd.stockNumber,0) + nvl(pdd.promotiondNum,0) + nvl(skd.seckillNum,0)) stockNumber, pd.afterSale, pd.gifts," +
				 " pd.onShelfTime, pd.offShelfTime," +
				 " pdd.status promotiondStatus, nvl(pdd.promotiondNum,0) promotiondNum," +
				 " skd.statu seckillStatus, nvl(skd.seckillNum,0) seckillNum " +
				 "from productData pd " +
				 "left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +
				 "left join seckillData skd on skd.shopProductId = pd.shopProductId " +
				 "order by pd.productName";
		
		Object[] params = new Object[]{userId, status, "%" + conditions.replaceAll("'", "''").replaceAll("\\s*", "").toLowerCase() + "%"};
		params = StringHelperTools.nullToString(params);
		
		logger.debug("查询通讯市场卖家产品列表:" +BaseUtil.logSQL(sql, params));
		
		return jdbcTemplate.query(sql, params, rowMapper);
	}
	
	/**通讯市场产品已上架、已下架产品   临时卖家*/
	@Override
	public List<ShopProductAreaVo> getTempSaleSaleListByStatus(String userId,String status, String conditions) {
		String sql = "with productData as( " +
				 " select sp.id as shopProductId, sp.productId, pm.productName, sp.supplyId, sa.id as supplyAreaId," +
				 " sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts," +
				 " sa.onShelfTime, sa.offShelfTime" +
				 " from shop_product sp " +
				 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " inner join tempsaler t on sp.supplyId = t.id " +
				 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " where t.userId = ? and sa.status = ? and sa.saleStatus <> '1' " +
				 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " and pm.saleType = '" + TellingConstance.SALETYPE_2 + "' and lower(replace(pm.productName, ' ', '')) like ? " +
				 " group by sp.id, sp.productId, pm.productName, sp.supplyId, sa.id, sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime " +
				 "), " +
				 "promotiondData as (" +
				 " select pdh.productId shopProductId, pdh.status, nvl(pdh.partiQuantity,0) - sum(nvl(od.product_num, 0)) promotiondNum " +
				 " from promotionm_hjh pmh " +
				 " inner join promotiond_hjh pdh on pmh.id = pdh.pId " +
				 " left join order_detail0 od  on od.active_id = pdh.id"
				 //+" and (od.order_type = '" + OrderConstantValues.ORDER_TYPE_13 +"' or od.order_type = '" + OrderConstantValues.ORDER_TYPE_19 + "')"
				 +" and  od.order_status <> '05' and od.order_status <> '06' and od.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " where pmh.isChecked = '2' and (pmh.protype = 2 or pmh.supplyid is not null) and (pdh.status = " + GroupBuyStatus.waitPulish.index() + " or pdh.status = " + GroupBuyStatus.inProcess.index() + " or pdh.status = " + GroupBuyStatus.intime.index() + ") " +
				 " group by pdh.productId, pdh.status, pdh.partiQuantity " +
				 "), " +
				 "seckillData as (" +
				 " select sk.productId shopProductId, sk.statu, sum(nvl(sk.order_num,0)) seckillNum " + 
				 " from seckill_hjh sk " +
				 " where sk.statu = 1 or sk.statu = 3 " +
				 " group by sk.productId, sk.statu " +
				 ") " +
				 "select pd.shopProductId, pd.productId, pd.productName, pd.supplyId, pd.supplyAreaId," +
				 " pd.priceRetailOnline,(nvl(pd.stockNumber,0) + nvl(pdd.promotiondNum,0) + nvl(skd.seckillNum,0)) stockNumber, pd.afterSale, pd.gifts," +
				 " pd.onShelfTime, pd.offShelfTime," +
				 " pdd.status promotiondStatus, nvl(pdd.promotiondNum,0) promotiondNum," +
				 " skd.statu seckillStatus, nvl(skd.seckillNum,0) seckillNum " +
				 "from productData pd " +
				 "left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +
				 "left join seckillData skd on skd.shopProductId = pd.shopProductId " +
				 "order by pd.productName";
		Object[] params = new Object[]{userId, status, "%" + conditions.replaceAll("'", "''").replaceAll("\\s*", "").toLowerCase() + "%"};
		params = StringHelperTools.nullToString(params);
		
		logger.debug("查询临时卖家产品列表:" +BaseUtil.logSQL(sql, params));
		
		return jdbcTemplate.query(sql, params, rowMapper);
	}
	
	/**数据转换*/
	private ParameterizedRowMapper<ShopProductAreaVo> rowMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setShopProductId(rs.getBigDecimal("shopProductId"));
			shopProductAreaVo.setProductId(rs.getBigDecimal("productId"));
			shopProductAreaVo.setProductName(rs.getString("productName"));
			shopProductAreaVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setAfterSale(rs.getString("afterSale"));
			shopProductAreaVo.setGifts(rs.getString("gifts"));
			shopProductAreaVo.setOnShelfTime(rs.getDate("onShelfTime"));
			shopProductAreaVo.setOffShelfTime(rs.getDate("offShelfTime"));
			shopProductAreaVo.setTopFlag("0");
			shopProductAreaVo.setPromotiondStatus(rs.getString("promotiondStatus"));
			shopProductAreaVo.setPromotiondNum(rs.getBigDecimal("promotiondNum"));
			shopProductAreaVo.setSeckillStatus(rs.getString("seckillStatus"));
			shopProductAreaVo.setSeckillNum(rs.getBigDecimal("seckillNum"));
			
			return shopProductAreaVo;
		}
	};
	
	/**根据供货商产品区域Id，和产品销售状态查询出VO信息*/
	@Override
	public ShopProductAreaVo getTelMarketShopProductAreaVoById(String supplyAreaId, String userId, String status) {
		String sql = "select sp.id as shopProductId, sa.id as supplyAreaId, sp.productId, sp.supplyId, pm.productName, sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime " +
				 " from shop_product sp " +
				 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " inner join supply s on sp.supplyId = s.id and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1+"' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " where s.user_id = '" + userId + "'" +
				 " and sa.id = " + supplyAreaId +" and sa.status = '" + status + "' and sa.saleStatus <> '1' " +
				 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " + 
				 " and pm.saleType = '" + TellingConstance.SALETYPE_2 + "' " +
				 " group by sp.id, sa.id, sp.productId, sp.supplyId, pm.productName, sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime " +
				 " order by pm.productName ";
		
		logger.debug("查询通讯市场卖家产品信息:" +BaseUtil.logSQL(sql, new Object[]{}));
		List<ShopProductAreaVo> list = jdbcTemplate.query(sql, tempMapper);
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	
	/**根据供货商产品区域Id，和产品销售状态查询出VO信息  临时卖家*/
	@Override
	public ShopProductAreaVo getTempSaleShopProductAreaVoById(String supplyAreaId, String userId, String status) {
		String sql = "select sp.id as shopProductId, sa.id as supplyAreaId, sp.productId, sp.supplyId, pm.productName, sa.priceRetailOnline, sa.overPlusNumber as stockNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime " +
				 " from shop_product sp " +
				 " inner join product_main pm on sp.productId = pm.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " inner join tempsaler t on sp.supplyId = t.id " +
				 " inner join supply_area0 sa on sp.id = sa.supplyProductId and sa.del_flag = '" + TellingConstance.DEL_FLAG_1+"' and sa.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " where t.userId = '" + userId + "'" +
				 " and sa.id = " + supplyAreaId +" and sa.status = '" + status + "' and sa.saleStatus <> '1' " +
				 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " +
				 " and pm.saleType = '" + TellingConstance.SALETYPE_2 + "' " +
				 " group by sp.id, sa.id, sp.productId, sp.supplyId, pm.productName, sa.priceRetailOnline, sa.overPlusNumber, sa.afterSale, sa.gifts, sa.onShelfTime, sa.offShelfTime " +
				 " order by pm.productName ";
		
		logger.debug("查询临时卖家产品信息:" +BaseUtil.logSQL(sql, new Object[]{}));
		List<ShopProductAreaVo> list = jdbcTemplate.query(sql, tempMapper);
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	
	/**数据转换*/
	private ParameterizedRowMapper<ShopProductAreaVo> tempMapper = new ParameterizedRowMapper<ShopProductAreaVo>() {
		@Override
		public ShopProductAreaVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ShopProductAreaVo shopProductAreaVo = new ShopProductAreaVo();
			shopProductAreaVo.setShopProductId(rs.getBigDecimal("shopProductId"));
			shopProductAreaVo.setSupplyAreaId(rs.getBigDecimal("supplyAreaId"));
			shopProductAreaVo.setSupplyId(rs.getBigDecimal("supplyId"));
			shopProductAreaVo.setProductId(rs.getBigDecimal("productId"));
			shopProductAreaVo.setProductName(rs.getString("productName"));
			shopProductAreaVo.setPriceRetailOnline(rs.getBigDecimal("priceRetailOnline"));
			shopProductAreaVo.setStockNumber(rs.getBigDecimal("stockNumber"));
			shopProductAreaVo.setAfterSale(rs.getString("afterSale"));
			shopProductAreaVo.setGifts(rs.getString("gifts"));
			shopProductAreaVo.setOnShelfTime(rs.getDate("onShelfTime"));
			shopProductAreaVo.setOffShelfTime(rs.getDate("offShelfTime"));
			shopProductAreaVo.setTopFlag("0");
			
			return shopProductAreaVo;
		}
	};
	
	/**修改通讯市场产品区域信息  价格和库存*/
	@Override
	public int updateShopProductAreaInfo(ShopProductAreaVo shopProductAreaVo, String userId) {
		String sql = "update supply_area0 set priceretailonline = ?, stocknumber = ?, overplusNumber = ?, afterSale = ?, gifts = ?, saleStatus = ?, update_user = ? where id = ? ";
		Object[] parmas = new Object[]{shopProductAreaVo.getPriceRetailOnline(), shopProductAreaVo.getStockNumber(), shopProductAreaVo.getStockNumber(),
								shopProductAreaVo.getAfterSale(), shopProductAreaVo.getGifts(),
								TellingConstance.SALESTATUS3,userId,shopProductAreaVo.getSupplyAreaId()
							};
		parmas = StringHelperTools.nullToString(parmas);
		logger.debug("修改通讯市场产品信息:" +BaseUtil.logSQL(sql, parmas));
		
		int count = jdbcTemplate.update(sql, parmas);
		return count;
	}
	/**批量更新通讯市场产品区域信息   上架状态 */
	@Override
	public int updateShopProductAreaOnSale(List<ShopProductAreaVo> shopProductAreaVoList, String status, String userId) {
		String sql = "update supply_area0 set priceretailonline = ?, stocknumber = ?, overplusNumber = ?, afterSale = ?, gifts = ?, status = ?, saleStatus = ?, update_user = ?, onShelfTime = sysdate, offShelfTime = '' where id = ? ";
		
		List<Object[]> paramsList = new ArrayList<Object[]>();
		for (ShopProductAreaVo shopProductAreaVo : shopProductAreaVoList) {
			Object[] parmas = new Object[]{shopProductAreaVo.getPriceRetailOnline(), shopProductAreaVo.getStockNumber(), shopProductAreaVo.getStockNumber(),
					shopProductAreaVo.getAfterSale(), shopProductAreaVo.getGifts(),
					status, TellingConstance.SALESTATUS3, userId, shopProductAreaVo.getSupplyAreaId()};
			parmas = StringHelperTools.nullToString(parmas);
			
			logger.debug("批量更新通讯市场产品上架状态:" +BaseUtil.logSQL(sql, parmas));
			
			paramsList.add(parmas);
		}
		
		int count[] = jdbcTemplate.batchUpdate(sql,paramsList);
		return count.length;
	}
	
	/**批量更新通讯市场产品区域信息   下架状态*/
	@Override
	public int updateShopProductAreaOffSale(List<String> supplyAreaIdList, String status, String userId) {
		String sql = "update supply_area0 set status = ?, saleStatus = ?, update_user = ?, onShelfTime = '', offShelfTime = sysdate where id = ? ";
		List<Object[]> paramsList = new ArrayList<Object[]>();
		if(supplyAreaIdList != null && supplyAreaIdList.size() > 0){
			for (int i = 0; i < supplyAreaIdList.size(); i++) {
				Object[] parmas = new Object[]{status, TellingConstance.SALESTATUS3, userId, supplyAreaIdList.get(i)};
				parmas = StringHelperTools.nullToString(parmas);
				
				logger.debug("批量更新通讯市场产品下架状态:" +BaseUtil.logSQL(sql, parmas));
				
				paramsList.add(parmas);
			}
			
			int count[] = jdbcTemplate.batchUpdate(sql,paramsList);
			return count.length;
		}
		return 0;
	}
	
	/**获得库存管控信息  供发短信使用*/
	@Override
	public List<ShopProductStockNumControlVo> getShopProductStockNumControlVoListBySms(int topValue) {
		String sql = "with productData as( " + 
					 " select pm.productId, sp.id  shopProductId, sp.supplyId, s.pri_mobile, sa.id supplyAreaId, sa.overplusnumber " + 
					 " from shop_product sp " + 
					 " inner join product_main pm on pm.productId = sp.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
					 " inner join supply s on s.id = sp.supplyId and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
					 " inner join supply_area0 sa on sa.supplyProductId = sp.id and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isvalid = '" + TellingConstance.IS_VALID_01 + "' " + 
					 " where pm.saletype = " + TellingConstance.SALETYPE_2 + 
					 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " + 
					 " and sa.salestatus = '" + TellingConstance.SALESTATUS3 + "' and sa.status = '" + TellingConstance.STATUS_1 +"' " + 
					 "), " + 
					 "promotiondData as (" +
					 " select pdh.productId shopProductId, pdh.status, nvl(pdh.partiQuantity,0) - sum(nvl(od.product_num, 0)) promotiondNum " +
					 " from promotionm_hjh pmh " +
					 " inner join promotiond_hjh pdh on pmh.id = pdh.pId " +
					 " left join order_detail0 od  on od.active_id = pdh.id"
					 //+" and (od.order_type = '" + OrderConstantValues.ORDER_TYPE_13 +"' or od.order_type = '" + OrderConstantValues.ORDER_TYPE_19 + "')"
					 +" and  od.order_status <> '05' and od.order_status <> '06' and od.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
					 " where pmh.isChecked = '2' and pmh.protype = 2 and (pdh.status = " + GroupBuyStatus.waitPulish.index() + " or pdh.status = " + GroupBuyStatus.inProcess.index() + " or pdh.status = " + GroupBuyStatus.intime.index() + ") " +
					 " group by pdh.productId, pdh.status, pdh.partiQuantity " +
					 "), " +
					 "seckillData as (" +
					 " select sk.productId shopProductId, sk.statu, sum(nvl(sk.order_num,0)) seckillNum " + 
					 " from seckill_hjh sk " +
					 " where sk.statu = 1 or sk.statu = 3 " +
					 " group by sk.productId, sk.statu " +
					 "), " +
					 "productDataInfo as ( " +
					 " select pd.shopProductId, pd.productId, pd.supplyId, pd.supplyAreaId, pd.pri_mobile, (pd.overplusnumber + nvl(pdd.promotiondNum,0) + nvl(skd.seckillNum,0)) stockNumber " +
					 " from productData pd " +
					 " left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +
					 " left join seckillData skd on skd.shopProductId = pd.shopProductId " +
					 "), " +
					 "topData as( " + 
					 " select productId from (" + 
					 "  select tss.productId, nvl(SUM(tss.saleqty), 0) product_sell_num " + 
					 "  from telmarket_sale_statics tss " + 
					 "  where tss.statics_date < trunc(sysdate, 'mm') " + 
					 "  and tss.statics_date >= trunc(add_months(sysdate, -1), 'mm') " + 
					 "  group by tss.productId" + 
					 "  order by product_sell_num desc " + 
					 " ) temp " + 
					 " where rownum <= " + topValue + " " + 
					 "), " + 
					 "resultData as( " + 
					 " select pdi.supplyId, pdi.pri_mobile, nvl2(td.productId, '1', '0') topFlag,  count(1) num " + 
					 " from productDataInfo pdi " + 
					 " left join topData td on td.productId = pdi.productId " + 
					 " where (td.productId is null and pdi.stockNumber < 3) or (td.productId is not null and pdi.stockNumber < 3) " + 
					 " group by pdi.supplyId, pdi.pri_mobile, td.productId " + 
					 " order by pdi.supplyId, pdi.pri_mobile " + 
					 ") " + 
					 "select supplyId, pri_mobile, topFlag, sum(num) total " + 
					 "from resultData " + 
					 "group by supplyId,pri_mobile,topFlag " + 
					 "order by supplyId,pri_mobile,topFlag ";
		logger.debug("获得库存管控信息供短信使用: " + sql);
		List<ShopProductStockNumControlVo> shopProductStockNumControlVoList = jdbcTemplate.query(sql, new RowMapper<ShopProductStockNumControlVo>(){
			@Override
			public ShopProductStockNumControlVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ShopProductStockNumControlVo shopProductStockNumControlVo = new ShopProductStockNumControlVo();
				shopProductStockNumControlVo.setSupplyId(rs.getString("supplyId"));
				shopProductStockNumControlVo.setPri_mobile(rs.getString("pri_mobile"));
				shopProductStockNumControlVo.setTopFlag(rs.getString("topFlag"));
				shopProductStockNumControlVo.setTotal(rs.getInt("total"));
				return shopProductStockNumControlVo;
			}
		});
						
		return shopProductStockNumControlVoList;
	}
	
	/**获得库存管控信息  供自动下架处理*/
	@Override
	public List<ShopProductStockNumControlVo> getShopProductStockNumControlVoListByOff(int topValue) {
		String sql = "with productData as( " + 
				 " select pm.productId, sp.id  shopProductId, sp.supplyId, s.pri_mobile, sa.id supplyAreaId, sa.overplusnumber " + 
				 " from shop_product sp " + 
				 " inner join product_main pm on pm.productId = sp.productId and pm.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
				 " inner join supply s on s.id = sp.supplyId and s.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " + 
				 " inner join supply_area0 sa on sa.supplyProductId = sp.id and sa.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sa.isvalid = '" + TellingConstance.IS_VALID_01 + "' " + 
				 " where pm.saletype = " + TellingConstance.SALETYPE_2 + 
				 " and sp.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' and sp.isValId = '" + TellingConstance.IS_VALID_01 + "' " + 
				 " and sa.salestatus = '" + TellingConstance.SALESTATUS3 + "' and sa.status = '" + TellingConstance.STATUS_1 +"' " + 
				 "), " + 
				 "promotiondData as (" +
				 " select pdh.productId shopProductId, pdh.status, nvl(pdh.partiQuantity,0) - sum(nvl(od.product_num, 0)) promotiondNum " +
				 " from promotionm_hjh pmh " +
				 " inner join promotiond_hjh pdh on pmh.id = pdh.pId " +
				 " left join order_detail0 od  on od.active_id = pdh.id"
				 //+" and (od.order_type = '" + OrderConstantValues.ORDER_TYPE_13 +"' or od.order_type = '" + OrderConstantValues.ORDER_TYPE_19 + "')"
				 +" and  od.order_status <> '05' and od.order_status <> '06' and od.del_flag = '" + TellingConstance.DEL_FLAG_1 + "' " +
				 " where pmh.isChecked = '2' and pmh.protype = 2 and (pdh.status = " + GroupBuyStatus.waitPulish.index() + " or pdh.status = " + GroupBuyStatus.inProcess.index() + " or pdh.status = " + GroupBuyStatus.intime.index() + ") " +
				 " group by pdh.productId, pdh.status, pdh.partiQuantity " +
				 "), " +
				 "seckillData as (" +
				 " select sk.productId shopProductId, sk.statu, sum(nvl(sk.order_num,0)) seckillNum " + 
				 " from seckill_hjh sk " +
				 " where sk.statu = 1 or sk.statu = 3 " +
				 " group by sk.productId, sk.statu " +
				 "), " +
				 "productDataInfo as ( " +
				 " select pd.shopProductId, pd.productId, pd.supplyId, pd.supplyAreaId, pd.pri_mobile, (pd.overplusnumber + nvl(pdd.promotiondNum,0) + nvl(skd.seckillNum,0)) stockNumber " +
				 " from productData pd " +
				 " left join promotiondData pdd on pdd.shopProductId = pd.shopProductId " +
				 " left join seckillData skd on skd.shopProductId = pd.shopProductId " +
				 " where pdd.status is null and skd.statu is null " +
				 "), " +
				 "topData as( " + 
				 " select productId from (" + 
				 "  select tss.productId, nvl(SUM(tss.saleqty), 0) product_sell_num " + 
				 "  from telmarket_sale_statics tss " + 
				 "  where tss.statics_date < trunc(sysdate, 'mm') " + 
				 "  and tss.statics_date >= trunc(add_months(sysdate, -1), 'mm') " + 
				 "  group by tss.productId" + 
				 "  order by product_sell_num desc " + 
				 " ) temp " + 
				 " where rownum <= " + topValue + " " + 
				 ") " + 
				 "select pdi.supplyId, pdi.pri_mobile, pdi.productId, pdi.supplyAreaId " + 
				 "from productDataInfo pdi " + 
				 "left join topData td on td.productId = pdi.productId " + 
				 "where (td.productId is null and pdi.stockNumber < 3) or (td.productId is not null and pdi.stockNumber < 3) ";
		logger.debug("获得库存管控信息供下架使用: " + sql);
		List<ShopProductStockNumControlVo> shopProductStockNumControlVoList = jdbcTemplate.query(sql, new RowMapper<ShopProductStockNumControlVo>(){
			@Override
			public ShopProductStockNumControlVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ShopProductStockNumControlVo shopProductStockNumControlVo = new ShopProductStockNumControlVo();
				shopProductStockNumControlVo.setSupplyId(rs.getString("supplyId"));
				shopProductStockNumControlVo.setPri_mobile(rs.getString("pri_mobile"));
				shopProductStockNumControlVo.setProductId(rs.getString("productId"));
				shopProductStockNumControlVo.setSupplyAreaId(rs.getString("supplyAreaId"));
				return shopProductStockNumControlVo;
			}
		});
						
		return shopProductStockNumControlVoList;
	}
}
